﻿--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$COLORS$$$$$$$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".colors (
  clr_id INTEGER NOT NULL, 
  clr_name VARCHAR(50) NOT NULL, 
  clr_code VARCHAR(20) NOT NULL, 
  CONSTRAINT colors_clr_code_key UNIQUE(clr_code), 
  CONSTRAINT pk_colors PRIMARY KEY(clr_id)
) WITHOUT OIDS;

CREATE TABLE "SKLAD".colorshistory (
  hclr_id INTEGER NOT NULL, 
  clr_id INTEGER, 
  clr_name VARCHAR(50), 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR NOT NULL, 
  old_clr_id INTEGER, 
  old_clr_name VARCHAR(50), 
  clr_code VARCHAR(20), 
  old_clr_code VARCHAR(20), 
  CONSTRAINT pk_colorshistory PRIMARY KEY(hclr_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGCOLORS" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Colorshistory(
              HCLR_ID,
              CLR_ID,
              CLR_NAME,
              CLR_CODE,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_COLORSHISTORY'),
              new.CLR_ID,
              new.CLR_NAME,
              new.CLR_CODE,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Colorshistory(
              HCLR_ID,
              CLR_ID,
              OLD_CLR_ID,
              CLR_NAME,
              OLD_CLR_NAME,
              CLR_CODE,
              OLD_CLR_CODE,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_COLORSHISTORY'),
              new.CLR_ID,
              old.CLR_ID,
              new.CLR_NAME,
              old.CLR_NAME,
              new.CLR_CODE,
              old.CLR_CODE,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Colorshistory(
              HCLR_ID,
              OLD_CLR_ID,
              OLD_CLR_NAME,
              OLD_CLR_CODE,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_COLORSHISTORY'),
              old.CLR_ID,
              old.CLR_NAME,
              old.CLR_CODE,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGCOLORS" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".colors FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGCOLORS"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$EXPENCES$$$$$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".expences (
  exp_id INTEGER NOT NULL, 
  ord_id INTEGER, 
  exp_date DATE NOT NULL, 
  exp_volume NUMERIC(12,4) NOT NULL, 
  CONSTRAINT pk_expences PRIMARY KEY(exp_id), 
  CONSTRAINT fk_expences_reference_orders FOREIGN KEY (ord_id)
    REFERENCES "SKLAD".orders(ord_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".expenceshistory (
  hexp_id INTEGER NOT NULL, 
  old_exp_id INTEGER, 
  exp_id INTEGER, 
  old_ord_id INTEGER, 
  ord_id INTEGER, 
  old_exp_date DATE, 
  exp_date DATE, 
  old_exp_volume NUMERIC(12,4), 
  exp_volume NUMERIC(12,4), 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR NOT NULL, 
  CONSTRAINT pk_expenceshistory PRIMARY KEY(hexp_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGEXPENCES" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Expenceshistory(
    		  HEXP_ID,
              EXP_ID,
              ORD_ID,
              EXP_DATE,
              EXP_VOLUME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_EXPENCESHISTORY'),
              new.EXP_ID,
              new.ORD_ID,
              new.EXP_DATE,
              new.EXP_VOLUME,    		                
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Expenceshistory(
    		  HEXP_ID,
              OLD_EXP_ID,
              EXP_ID,
              OLD_ORD_ID,
              ORD_ID,
              OLD_EXP_DATE,
              EXP_DATE,
              OLD_EXP_VOLUME,
              EXP_VOLUME,              
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_EXPENCESHISTORY'),
			  old.EXP_ID,
              new.EXP_ID,
              old.ORD_ID,
              new.ORD_ID,
              old.EXP_DATE,
              new.EXP_DATE,
              old.EXP_VOLUME,
              new.EXP_VOLUME,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Expenceshistory(
    		  HEXP_ID,
              OLD_EXP_ID,
              OLD_ORD_ID,
              OLD_EXP_DATE,
              OLD_EXP_VOLUME,            
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_EXPENCESHISTORY'),
			  old.EXP_ID,
              old.ORD_ID,
              old.EXP_DATE,
              old.EXP_VOLUME,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGEXPENCES" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".expences FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGEXPENCES"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$NOMENCLATURES$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".nomenclatures (
  nom_id INTEGER NOT NULL, 
  nom_name VARCHAR(100) NOT NULL, 
  CONSTRAINT pk_nomenclatures PRIMARY KEY(nom_id)
) WITHOUT OIDS;

CREATE TABLE "SKLAD".nomenclatureshistory (
  hnom_id INTEGER NOT NULL, 
  old_nom_id INTEGER, 
  nom_id INTEGER, 
  old_nom_name VARCHAR(100), 
  nom_name VARCHAR(100), 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT nomenclatureshistory_pkey PRIMARY KEY(hnom_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGNOMENCLATURES" (
)
RETURNS trigger AS
$body$
BEGIN
 if TG_OP = 'INSERT' then
    insert into "SKLAD".nomenclatureshistory(
              HNOM_ID,
              NOM_ID,
              NOM_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_NOMENCLATURESHISTORY'),
              new.NOM_ID,
              new.NOM_NAME,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".nomenclatureshistory(
              HNOM_ID,
              OLD_NOM_ID,
              NOM_ID,
              OLD_NOM_NAME,
              NOM_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_NOMENCLATURESHISTORY'),
              old.NOM_ID,
              new.NOM_ID,
              old.NOM_NAME,
              new.NOM_NAME,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".nomenclatureshistory(
              HNOM_ID,
              OLD_NOM_ID,
              OLD_NOM_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_NOMENCLATURESHISTORY'),
              old.NOM_ID,
              old.NOM_NAME,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;  
EXCEPTION
  WHEN others THEN
    raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGNOMENCLATURES" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".nomenclatures FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGNOMENCLATURES"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$OBJECTS$$$$$$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".objects (
  obj_id INTEGER NOT NULL, 
  obj_name VARCHAR(100) NOT NULL, 
  obj_address VARCHAR(200) NOT NULL, 
  obj_datefill DATE NOT NULL, 
  usr_id INTEGER NOT NULL, 
  CONSTRAINT pk_object PRIMARY KEY(obj_id), 
  CONSTRAINT fk_objects_reference_users FOREIGN KEY (usr_id)
    REFERENCES "SKLAD".users(usr_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".objectshistory (
  hobj_id INTEGER NOT NULL, 
  old_obj_id INTEGER, 
  obj_id INTEGER, 
  old_obj_name VARCHAR(100), 
  obj_name VARCHAR(100), 
  old_obj_address VARCHAR(200), 
  obj_address VARCHAR(200), 
  old_obj_datefill DATE, 
  obj_datefill DATE, 
  old_usr_id INTEGER, 
  usr_id INTEGER, 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT pk_objecthistory PRIMARY KEY(hobj_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGOBJECTS" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".objectshistory(
			  HOBJ_ID,
              OBJ_ID,
              OBJ_NAME,
              OBJ_ADDRESS,
              OBJ_DATEFILL,
              USR_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_OBJECTSHISTORY'),
			  NEW.OBJ_ID,
              NEW.OBJ_NAME,
              NEW.OBJ_ADDRESS,
              NEW.OBJ_DATEFILL,
              NEW.USR_ID,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Objectshistory(
			  HOBJ_ID,
              OLD_OBJ_ID,
              OBJ_ID,
              OLD_OBJ_NAME,
              OBJ_NAME,
              OLD_OBJ_ADDRESS,
              OBJ_ADDRESS,
              OLD_OBJ_DATEFILL,
              OBJ_DATEFILL,
              OLD_USR_ID,
              USR_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_OBJECTSHISTORY'),
			  old.OBJ_ID,
              new.OBJ_ID,
              old.OBJ_NAME,
              new.OBJ_NAME,
              old.OBJ_ADDRESS,
              new.OBJ_ADDRESS,
              old.OBJ_DATEFILL,
              new.OBJ_DATEFILL,
              old.USR_ID,
              new.USR_ID,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Objectshistory(
			  HOBJ_ID,
              OLD_OBJ_ID,
              OLD_OBJ_NAME,
              OLD_OBJ_ADDRESS,
              OLD_OBJ_DATEFILL,
              OLD_USR_ID,              
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_OBJECTSHISTORY'),
			  old.OBJ_ID,
              old.OBJ_NAME,
              old.OBJ_ADDRESS,
              old.OBJ_DATEFILL,
              OLD.USR_ID,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGOBJECTS" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".objects FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGOBJECTS"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$ORDERNOMENCLATURES$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".ordernomenclatures (
  ord_id INTEGER NOT NULL, 
  nom_id INTEGER NOT NULL, 
  nom_amount NUMERIC(12,4) NOT NULL, 
  stf_id INTEGER NOT NULL, 
  exp_plan NUMERIC(12,4), 
  exp_fact NUMERIC(12,4), 
  CONSTRAINT ordernomenclatures_pkey PRIMARY KEY(ord_id, nom_id), 
  CONSTRAINT fk_ordernomenclatures_nom FOREIGN KEY (nom_id)
    REFERENCES "SKLAD".nomenclatures(nom_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT fk_ordernomenclatures_ord FOREIGN KEY (ord_id)
    REFERENCES "SKLAD".orders(ord_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT ordernomenclatures_fk FOREIGN KEY (stf_id)
    REFERENCES "SKLAD".stuff(stf_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".ordernomenclatureshistory (
  honm_id INTEGER NOT NULL, 
  ord_id INTEGER, 
  nom_id INTEGER, 
  nom_amount NUMERIC(12,4), 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  old_ord_id INTEGER, 
  old_nom_id INTEGER, 
  old_nom_amount NUMERIC(12,4), 
  old_stf_id INTEGER, 
  stf_id INTEGER, 
  exp_plan NUMERIC(12,4), 
  old_exp_plan NUMERIC(12,4), 
  old_exp_fact NUMERIC(12,4), 
  exp_fact NUMERIC(12,4), 
  CONSTRAINT ordernomenclatureshistory_pkey PRIMARY KEY(honm_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGORDERNOMENCLATURES" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".ordernomenclatureshistory(
              HONM_ID,
              ORD_ID,
              NOM_ID,
              NOM_AMOUNT,
              STF_ID,
              EXP_PLAN,
              EXP_FACT,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERNOMENCLATURESHISTORY'),
              new.ORD_ID,
              new.NOM_ID,
              new.NOM_AMOUNT,
              new.STF_ID,
              new.EXP_PLAN,
              new.EXP_FACT,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".ordernomenclatureshistory(
              HONM_ID,
              ORD_ID,
              OLD_ORD_ID,
              NOM_ID,
              OLD_NOM_ID,
              NOM_AMOUNT,
              OLD_NOM_AMOUNT,
              STF_ID,
              OLD_STF_ID,
              EXP_PLAN,
              OLD_EXP_PLAN,
              EXP_FACT,
              OLD_EXP_FACT,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERNOMENCLATURESHISTORY'),
              new.ORD_ID,
              old.ORD_ID,
              new.NOM_ID,
              old.NOM_ID,
              new.NOM_AMOUNT,
              old.NOM_AMOUNT,
              new.STF_ID,
              old.STF_ID,
              new.EXP_PLAN,
              old.EXP_PLAN,
              new.EXP_FACT,
              old.EXP_FACT,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".ordernomenclatureshistory(
              HONM_ID,
              OLD_ORD_ID,
              OLD_NOM_ID,
              OLD_NOM_AMOUNT,
              OLD_STF_ID,
              OLD_EXP_PLAN,
              OLD_EXP_FACT,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERNOMENCLATURESHISTORY'),
              old.ORD_ID,
              old.NOM_ID,
              old.NOM_AMOUNT,
              old.STF_ID,
              old.EXP_PLAN,
              old.EXP_FACT,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGORDERNOMENCLATURES" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".ordernomenclatures FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGORDERNOMENCLATURES"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$ORDERPARAMSVALUES$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".orderparamsvalues (
  ord_id INTEGER NOT NULL, 
  val_id INTEGER NOT NULL, 
  CONSTRAINT orderparamsvalues_pkey PRIMARY KEY(ord_id, val_id), 
  CONSTRAINT orderparamsvalues_fk FOREIGN KEY (ord_id)
    REFERENCES "SKLAD".orders(ord_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE, 
  CONSTRAINT orderparamsvalues_fk1 FOREIGN KEY (val_id)
    REFERENCES "SKLAD".valueparams(val_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".orderparamsvalueshistory (
  hopv_id INTEGER NOT NULL, 
  old_ord_id INTEGER, 
  ord_id INTEGER, 
  old_val_id INTEGER, 
  val_id INTEGER, 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT orderparamsvalueshistory_pkey PRIMARY KEY(hopv_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGORDERPARAMSVALUES" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".orderparamsvalueshistory(
              HOPV_ID,
              ORD_ID,
              VAL_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERPARAMSVALUESHISTORY'),
              new.ORD_ID,
              new.VAL_ID,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".orderparamsvalueshistory(
              HOPV_ID,
              ORD_ID,
              OLD_ORD_ID,
              VAL_ID,
              OLD_VAL_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERPARAMSVALUESHISTORY'),
              new.ORD_ID,
              old.ORD_ID,
              new.VAL_ID,
              old.VAL_ID,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".orderparamsvalueshistory(
              HOPV_ID,
              OLD_ORD_ID,
              OLD_VAL_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERPARAMSVALUESHISTORY'),
              old.ORD_ID,
              old.VAL_ID,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGORDERPARAMSVALUES" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".orderparamsvalues FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGORDERPARAMSVALUES"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$ORDERS$$$$$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".orders (
  ord_id INTEGER NOT NULL, 
  obj_id INTEGER NOT NULL, 
  ord_number VARCHAR(10) NOT NULL, 
  ord_datefill DATE NOT NULL, 
  ord_datecomplete DATE, 
  usr_id INTEGER NOT NULL, 
  CONSTRAINT pk_orders PRIMARY KEY(ord_id), 
  CONSTRAINT fk_orders_reference_objects FOREIGN KEY (obj_id)
    REFERENCES "SKLAD".objects(obj_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE, 
  CONSTRAINT fk_orders_reference_users FOREIGN KEY (usr_id)
    REFERENCES "SKLAD".users(usr_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".ordershistory (
  hord_id INTEGER NOT NULL, 
  old_ord_id INTEGER, 
  ord_id INTEGER, 
  old_obj_id INTEGER, 
  obj_id INTEGER, 
  old_ord_number VARCHAR(10), 
  ord_number VARCHAR(10), 
  old_ord_datefill DATE, 
  ord_datefill DATE, 
  old_ord_datecomplete DATE, 
  ord_datecomplete DATE, 
  old_usr_id INTEGER, 
  usr_id INTEGER, 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT pk_ordershistory PRIMARY KEY(hord_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGORDERS" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Ordershistory(
              HORD_ID,
			  ORD_ID,
              OBJ_ID,
              ORD_NUMBER,
              ORD_DATEFILL,
              ORD_DATECOMPLETE,
              USR_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERSHISTORY'),
	 		  new.ORD_ID,
              new.OBJ_ID,
              new.ORD_NUMBER,
              new.ORD_DATEFILL,
              new.ORD_DATECOMPLETE,
              new.USR_ID,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Ordershistory(
              HORD_ID,
              OLD_ORD_ID,
			  ORD_ID,
              OLD_OBJ_ID,
              OBJ_ID,
              OLD_ORD_NUMBER,
              ORD_NUMBER,
              OLD_ORD_DATEFILL,
              ORD_DATEFILL,
              OLD_ORD_DATECOMPLETE,
              ORD_DATECOMPLETE,
              OLD_USR_ID,
              USR_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERSHISTORY'),
			  old.ORD_ID,
              new.ORD_ID,
              old.OBJ_ID,
              new.OBJ_ID,
              old.ORD_NUMBER,
              new.ORD_NUMBER,
              old.ORD_DATEFILL,
              new.ORD_DATEFILL,
              old.ORD_DATECOMPLETE,
              NEW.ORD_DATECOMPLETE,
              old.USR_ID,
              new.USR_ID,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Ordershistory(
              HORD_ID,
			  OLD_ORD_ID,
              OLD_OBJ_ID,
              OLD_ORD_NUMBER,
              OLD_ORD_DATEFILL,
              OLD_ORD_DATECOMPLETE,
              OLD_USR_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_ORDERSHISTORY'),
			  old.ORD_ID,
              old.OBJ_ID,
              old.ORD_NUMBER,
              old.ORD_DATEFILL,
              old.ORD_DATECOMPLETE,
              old.USR_ID,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;  
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGORDERS" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".orders FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGORDERS"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$PARAMSLIBRARY$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".paramslibrary (
  lib_id INTEGER NOT NULL, 
  lib_name VARCHAR(100) NOT NULL, 
  lib_type VARCHAR(1) NOT NULL, 
  CONSTRAINT pk_paramslibrary PRIMARY KEY(lib_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGPARAMSLIBRARY" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Paramslibraryhistory(
			  HLIB_ID,
              LIB_ID,
              LIB_NAME,
              LIB_TYPE,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PARAMSLIBRARYHISTORY'),
			  NEW.LIB_ID,
              new.LIB_NAME,
              new.LIB_TYPE,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Paramslibraryhistory(
			  HLIB_ID,
              OLD_LIB_ID,
              LIB_ID,
              OLD_LIB_NAME,
              LIB_NAME,
              OLD_LIB_TYPE,
              LIB_TYPE,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PARAMSLIBRARYHISTORY'),
			  old.LIB_ID,
              new.LIB_ID,
              old.LIB_NAME,
              new.LIB_NAME,
              old.LIB_TYPE,
              new.LIB_TYPE,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Paramslibraryhistory(
			  HLIB_ID,
              OLD_LIB_ID,
              OLD_LIB_NAME,
              OLD_LIB_TYPE,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PARAMSLIBRARYHISTORY'),
			  old.LIB_ID,
              old.LIB_NAME,
              old.LIB_TYPE,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW; 
EXCEPTION
WHEN OTHERS THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TABLE "SKLAD".paramslibraryhistory (
  hlib_id INTEGER NOT NULL, 
  old_lib_id INTEGER, 
  lib_id INTEGER, 
  old_lib_name VARCHAR(100), 
  lib_name VARCHAR(100), 
  old_lib_type VARCHAR(1), 
  lib_type VARCHAR(1), 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT pk_paramslibraryhistory PRIMARY KEY(hlib_id)
) WITHOUT OIDS;

CREATE TRIGGER "TRGPARAMSLIBRARY" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".paramslibrary FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGPARAMSLIBRARY"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$PARAMSLIBRARY$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".paramslibrarycust (
  plc_id INTEGER NOT NULL, 
  nom_id INTEGER NOT NULL, 
  lib_id INTEGER NOT NULL, 
  plc_req BOOLEAN NOT NULL, 
  CONSTRAINT pk_paramslibrarycust PRIMARY KEY(plc_id), 
  CONSTRAINT fk_paramsli_reference_nomencla FOREIGN KEY (nom_id)
    REFERENCES "SKLAD".nomenclatures(nom_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE, 
  CONSTRAINT fk_paramsli_reference_paramsli FOREIGN KEY (lib_id)
    REFERENCES "SKLAD".paramslibrary(lib_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".paramslibrarycusthistory (
  hplc_id INTEGER NOT NULL, 
  old_plc_id INTEGER, 
  plc_id INTEGER, 
  old_nom_id INTEGER, 
  nom_id INTEGER, 
  old_lib_id INTEGER, 
  lib_id INTEGER, 
  old_plc_req BOOLEAN, 
  plc_req BOOLEAN, 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT pk_paramslibrarycusthistory PRIMARY KEY(hplc_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGPARAMSLIBRARYCUST" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Paramslibrarycusthistory(
			  HPLC_ID, 
              PLC_ID,
              NOM_ID,
              LIB_ID,
              PLC_REQ,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PARAMSLIBRARYCUSTHISTORY'),
			  new.PLC_ID,
              new.NOM_ID,
              new.LIB_ID,
              new.PLC_REQ,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Paramslibrarycusthistory(
			  HPLC_ID,
              OLD_PLC_ID,
              PLC_ID,
              OLD_NOM_ID,
              NOM_ID,
              OLD_LIB_ID,
              LIB_ID,
              OLD_PLC_REQ,
              PLC_REQ,           
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PARAMSLIBRARYCUSTHISTORY'),
			  old.PLC_ID,
              new.PLC_ID,
              old.NOM_ID,
              new.NOM_ID,
              old.LIB_ID,
              new.LIB_ID,
              old.PLC_REQ,
              new.PLC_REQ,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Paramslibrarycusthistory(
			  HPLC_ID,
              OLD_PLC_ID,
              OLD_NOM_ID,
              OLD_LIB_ID,
              OLD_PLC_REQ,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PARAMSLIBRARYCUSTHISTORY'),
			  old.PLC_ID,
              old.NOM_ID,
              old.LIB_ID,
              old.PLC_REQ,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN OTHERS THEN
  RAISE;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGPARAMSLIBRARYCUST" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".paramslibrarycust FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGPARAMSLIBRARYCUST"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$PRODUCERS$$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".producers (
  prc_id INTEGER NOT NULL, 
  prc_name VARCHAR(100) NOT NULL, 
  CONSTRAINT pk_producers PRIMARY KEY(prc_id)
) WITHOUT OIDS;

COMMENT ON TABLE "SKLAD".producers
IS 'Таблица производителей';

CREATE TABLE "SKLAD".producershistory (
  hprc_id INTEGER NOT NULL, 
  old_prc_id INTEGER, 
  prc_id INTEGER, 
  old_prc_name VARCHAR(100), 
  prc_name VARCHAR(100), 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT pk_producershistory PRIMARY KEY(hprc_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGPRODUCERS" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Producershistory(
			  HPRC_ID,
              PRC_ID,
              PRC_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PRODUCERSHISTORY'),
			  new.PRC_ID,
              new.PRC_NAME,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Producershistory(
			  HPRC_ID,
              OLD_PRC_ID,
              PRC_ID,
              OLD_PRC_NAME,
              PRC_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PRODUCERSHISTORY'),
			  old.PRC_ID,
              new.PRC_ID,
              old.PRC_NAME,
              new.PRC_NAME,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Producershistory(
			  HPRC_ID,
              OLD_PRC_ID,
              OLD_PRC_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_PRODUCERSHISTORY'),
			  old.PRC_ID,
              old.PRC_NAME,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;  
EXCEPTION
WHEN OTHERS THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGPRODUCERS" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".producers FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGPRODUCERS"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$RASHODSKLAD$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".rashodsklad (
  exp_id INTEGER NOT NULL, 
  sty_id INTEGER NOT NULL, 
  CONSTRAINT pk_rashodsklad PRIMARY KEY(exp_id, sty_id), 
  CONSTRAINT fk_rashodsk_reference_expences FOREIGN KEY (exp_id)
    REFERENCES "SKLAD".expences(exp_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE, 
  CONSTRAINT fk_rashodsk_reference_stuffyar FOREIGN KEY (sty_id)
    REFERENCES "SKLAD".stuffyard(sty_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".rashodskladhistory (
  hrsl_id INTEGER NOT NULL, 
  old_exp_id INTEGER, 
  exp_id INTEGER, 
  old_sty_id INTEGER, 
  sty_id INTEGER, 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10), 
  CONSTRAINT rashodskladhistory_pkey PRIMARY KEY(hrsl_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGRASHODSKLAD" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".rashodskladhistory(
              HRSL_ID,
              EXP_ID,
              STY_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_RASHODSKLADHISTORY'),
              new.EXP_ID,
              new.STY_ID,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".rashodskladhistory(
              HRSL_ID,
              EXP_ID,
              OLD_EXP_ID,
              STY_ID,
              OLD_STY_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_RASHODSKLADHISTORY'),
              new.EXP_ID,
              old.EXP_ID,
              new.STY_ID,
              old.STY_ID,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".rashodskladhistory(
              HRSL_ID,
              OLD_EXP_ID,
              OLD_STY_ID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_RASHODSKLADHISTORY'),
              old.EXP_ID,
              old.STY_ID,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;  
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGRASHODSKLAD" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".rashodsklad FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGRASHODSKLAD"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$$$$STUFF$$$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".stuff (
  stf_id INTEGER NOT NULL, 
  clr_id INTEGER NOT NULL, 
  stt_id INTEGER NOT NULL, 
  prc_id INTEGER NOT NULL, 
  stf_desc VARCHAR(100), 
  stf_thick NUMERIC(12,4) NOT NULL, 
  stf_koef NUMERIC(12,4) NOT NULL, 
  CONSTRAINT pk_stuff PRIMARY KEY(stf_id), 
  CONSTRAINT fk_stuff_reference_colors FOREIGN KEY (clr_id)
    REFERENCES "SKLAD".colors(clr_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE, 
  CONSTRAINT fk_stuff_reference_producer FOREIGN KEY (prc_id)
    REFERENCES "SKLAD".producers(prc_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE, 
  CONSTRAINT fk_stuff_reference_stufftyp FOREIGN KEY (stt_id)
    REFERENCES "SKLAD".stufftype(stt_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".stuffhistory (
  hstf_id INTEGER NOT NULL, 
  old_stf_id INTEGER, 
  stf_id INTEGER, 
  old_clr_id INTEGER, 
  clr_id INTEGER, 
  old_stt_id INTEGER, 
  stt_id INTEGER, 
  old_prc_id INTEGER, 
  prc_id INTEGER, 
  old_stf_desc VARCHAR(100), 
  stf_desc VARCHAR(100), 
  old_stf_thick NUMERIC(12,4), 
  stf_thick NUMERIC(12,4), 
  old_stf_koef NUMERIC(12,4), 
  stf_koef NUMERIC(12,4), 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT pk_stuffhistory PRIMARY KEY(hstf_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGSTUFF" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Stuffhistory(
    		  HSTF_ID,
			  STF_ID,
              CLR_ID,
              STT_ID,
              PRC_ID,
              STF_DESC,
              STF_THICK,
              STF_KOEF,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFSHISTORY'),
			  new.STF_ID,
              new.CLR_ID,
              new.STT_ID,
              new.PRC_ID,
              new.STF_DESC,
              new.STF_THICK,
              new.STF_KOEF,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Stuffhistory(
    		  HSTF_ID,
              OLD_STF_ID,
			  STF_ID,
              OLD_CLR_ID,
              CLR_ID,
              OLD_STT_ID,
              STT_ID,
              OLD_PRC_ID,
              PRC_ID,
              OLD_STF_DESC,
              STF_DESC,
              OLD_STF_THICK,
              STF_THICK,
              OLD_STF_KOEF,
              STF_KOEF,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFSHISTORY'),
			  old.STF_ID,
              new.STF_ID,
              old.CLR_ID,
              new.CLR_ID,
              old.STT_ID,
              new.STT_ID,
              OLD.PRC_ID,
              NEW.PRC_ID,
              old.STF_DESC,
              new.STF_DESC,
              old.STF_THICK,
              new.STF_THICK,
              old.STF_KOEF,
              new.STF_KOEF,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Stuffhistory(
			  HSTF_ID,
			  OLD_STF_ID,
              OLD_CLR_ID,
              OLD_STT_ID,
              OLD_PRC_ID,
              OLD_STF_DESC,
              OLD_STF_THICK,
              OLD_STF_KOEF,              
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFSHISTORY'),
			  old.STF_ID,
              old.CLR_ID,
              old.STT_ID,
              old.PRC_ID,
              OLD.STF_DESC,
              old.STF_THICK,
              old.STF_KOEF,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN OTHERS THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGSTUFF" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".stuff FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGSTUFF"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$$STUFFTYPE$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".stufftype (
  stt_id INTEGER NOT NULL, 
  stt_name VARCHAR(100) NOT NULL, 
  CONSTRAINT pk_stufftype PRIMARY KEY(stt_id)
) WITHOUT OIDS;

CREATE TABLE "SKLAD".stufftypehistory (
  hstt_id INTEGER NOT NULL, 
  old_stt_id INTEGER, 
  stt_id INTEGER, 
  old_stt_name VARCHAR(100), 
  stt_name VARCHAR(100), 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT pk_stufftypehistory PRIMARY KEY(hstt_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGSTUFFTYPE" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".stufftypehistory(
    		  HSTT_ID,
              STT_ID,
              STT_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFTYPESHISTORY'),
			  new.STT_ID,
              new.STT_NAME,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".stufftypehistory(
    		  HSTT_ID,
			  OLD_STT_ID,
              STT_ID,
              OLD_STT_NAME,
              STT_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFTYPESHISTORY'),
			  old.STT_ID,
              new.STT_ID,
              old.STT_NAME,
              new.STT_NAME,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".stufftypehistory(
    		  HSTT_ID,
			  OLD_STT_ID,
              OLD_STT_NAME,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFTYPESHISTORY'),
			  old.STT_ID,
              old.STT_NAME,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN OTHERS THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGSTUFFTYPE" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".stufftype FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGSTUFFTYPE"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$$STUFFYARD$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".stuffyard (
  sty_id INTEGER NOT NULL, 
  stf_id INTEGER NOT NULL, 
  sty_square NUMERIC(12,4) NOT NULL, 
  sty_date DATE NOT NULL, 
  sty_woff BOOLEAN NOT NULL, 
  sty_mass NUMERIC(12,4) NOT NULL, 
  CONSTRAINT pk_stuffyard PRIMARY KEY(sty_id), 
  CONSTRAINT fk_stuffyar_reference_stuff FOREIGN KEY (stf_id)
    REFERENCES "SKLAD".stuff(stf_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".stuffyardhistory (
  hsty_id INTEGER NOT NULL, 
  old_sty_id INTEGER, 
  sty_id INTEGER, 
  old_stf_id INTEGER, 
  stf_id INTEGER, 
  old_sty_square NUMERIC(12,4), 
  sty_square NUMERIC(12,4), 
  old_sty_date DATE, 
  sty_date DATE, 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR NOT NULL, 
  sty_woff BOOLEAN, 
  old_sty_woff BOOLEAN, 
  old_sty_mass NUMERIC(12,4), 
  sty_mass NUMERIC(12,4), 
  CONSTRAINT pk_stuffyardhistory PRIMARY KEY(hsty_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGSTUFFYARD" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Stuffyardhistory(
    		  HSTY_ID,
			  STY_ID,
              STF_ID,
              STY_SQUARE,
              STY_MASS,
              STY_DATE,
              STY_WOFF,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFYARDHISTORY'),
			  new.STY_ID,
              new.STF_ID,
              new.STY_SQUARE,
              new.STY_MASS,
              new.STY_DATE,
              new.STY_WOFF,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Stuffyardhistory(
			  HSTY_ID,
              OLD_STY_ID,
              STY_ID,
              OLD_STF_ID,
              STF_ID,
              OLD_STY_SQUARE,
              STY_SQUARE,
              OLD_STY_MASS,
              STY_MASS,
              OLD_STY_DATE,
              STY_DATE,
              OLD_STY_WOFF,
              STY_WOFF,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFYARDHISTORY'),
			  old.STY_ID,
              new.STY_ID,
              old.STF_ID,
              new.STF_ID,
              old.STY_SQUARE,
              new.STY_SQUARE,
              old.STY_MASS,
              new.STY_MASS,
              old.STY_DATE,
              new.STY_DATE,
              old.STY_WOFF,
              new.STY_WOFF,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Stuffyardhistory(
    		  HSTY_ID,
			  OLD_STY_ID,
              OLD_STF_ID,
              OLD_STY_SQUARE,
              OLD_STY_MASS,
              OLD_STY_DATE,
              OLD_STY_WOFF,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_STUFFYARDHISTORY'),
			  old.STY_ID,
              old.STF_ID,
              old.STY_SQUARE,
              old.STY_MASS,
              old.STY_DATE,
              old.STY_WOFF,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGSTUFFYARD" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".stuffyard FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGSTUFFYARD"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$$$$USERS$$$$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".users (
  usr_id INTEGER NOT NULL, 
  usr_name VARCHAR(50) NOT NULL, 
  usesysid INTEGER NOT NULL, 
  roleid INTEGER, 
  CONSTRAINT pk_users PRIMARY KEY(usr_id), 
  CONSTRAINT "users_USESYSID_key" UNIQUE(usesysid)
) WITHOUT OIDS;

COMMENT ON COLUMN "SKLAD".users.usr_id
IS 'Идентификатор пользователя';

COMMENT ON COLUMN "SKLAD".users.usr_name
IS 'ФИО пользователя';

COMMENT ON COLUMN "SKLAD".users.usesysid
IS 'Идентификатор пользователя БД';

COMMENT ON COLUMN "SKLAD".users.roleid
IS 'Идентификатор роли выданой пользователю';


CREATE TABLE "SKLAD".usershistory (
  husr_id INTEGER NOT NULL, 
  old_usr_id INTEGER, 
  usr_id INTEGER, 
  old_usr_name VARCHAR(50), 
  usr_name VARCHAR(50), 
  old_usesysid INTEGER, 
  usesysid INTEGER, 
  old_roleid INTEGER, 
  roleid INTEGER, 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR(10) NOT NULL, 
  CONSTRAINT pk_usershistory PRIMARY KEY(husr_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGUSERS" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Usershistory(
              HUSR_ID,
			  USR_ID,
              USR_NAME,
              USESYSID,
              ROLEID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_USERSHISTORY'),
	          new.usr_id,
              new.usr_name,
              new.usesysid,
              new.roleid,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Usershistory(
              HUSR_ID,
              OLD_USR_ID,
			  USR_ID,
              OLD_USR_NAME,
              USR_NAME,
              OLD_USESYSID,
              USESYSID,
              OLD_ROLEID,
              ROLEID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_USERSHISTORY'),
			  old.usr_id,
              new.usr_id,
              old.usr_name,
              new.usr_name,
              old.usesysid,
              new.usesysid,
              old.roleid,
              new.roleid,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Usershistory(
              HUSR_ID,
			  OLD_USR_ID,
              OLD_USR_NAME,
              OLD_USESYSID,
              OLD_ROLEID,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_USERSHISTORY'),
			  old.usr_id,
              old.usr_name,
              old.usesysid,
              old.roleid,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGUSERS" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".users FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGUSERS"();

--$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--$$$$$$$$$$$$$$$$$$$$$$VALUEPARAMS$$$$$$$$$$$$$$$$$$$$$$
CREATE TABLE "SKLAD".valueparams (
  val_id INTEGER NOT NULL, 
  plc_id INTEGER NOT NULL, 
  val_varchar VARCHAR(500), 
  val_num NUMERIC(10,4), 
  val_date DATE, 
  grp_id INTEGER, 
  CONSTRAINT pk_valueparams PRIMARY KEY(val_id), 
  CONSTRAINT fk_valuepar_reference_paramsli FOREIGN KEY (plc_id)
    REFERENCES "SKLAD".paramslibrarycust(plc_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE "SKLAD".valueparamshistory (
  hval_id INTEGER NOT NULL, 
  old_val_id INTEGER, 
  val_id INTEGER, 
  old_plc_id INTEGER, 
  plc_id INTEGER, 
  old_val_varchar VARCHAR(500), 
  val_varchar VARCHAR(500), 
  old_val_num NUMERIC(12,4), 
  val_num NUMERIC(12,4), 
  old_val_date DATE, 
  val_date DATE, 
  username VARCHAR(200) NOT NULL, 
  change_date DATE NOT NULL, 
  operation VARCHAR NOT NULL, 
  old_grp_id INTEGER, 
  grp_id INTEGER, 
  CONSTRAINT pk_valueparamshistory PRIMARY KEY(hval_id)
) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION "SKLAD"."F$TRGVALUEPARAMS" (
)
RETURNS trigger AS
$body$
BEGIN
  if TG_OP = 'INSERT' then
    insert into "SKLAD".Valueparamshistory(
			  HVAL_ID,
              VAL_ID,
              PLC_ID,
              GRP_ID,
              VAL_VARCHAR,
              VAL_NUM,
              VAL_DATE,
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_VALUEPARAMSHISTORY'),
			  new.VAL_ID,
              new.PLC_ID,
              new.GRP_ID,
              new.VAL_VARCHAR,
              new.VAL_NUM,
              new.VAL_DATE,
              CURRENT_USER, NOW(), TG_OP);
  ELSE IF TG_OP = 'UPDATE' then
    insert into "SKLAD".Valueparamshistory(
			  HVAL_ID,
              OLD_VAL_ID,
              VAL_ID,
              OLD_PLC_ID,
              PLC_ID,
              OLD_GRP_ID,
              GRP_ID,
              OLD_VAL_VARCHAR,
              VAL_VARCHAR,
              OLD_VAL_NUM,
              VAL_NUM,
              OLD_VAL_DATE,
              VAL_DATE,              
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_VALUEPARAMSHISTORY'),
			  old.VAL_ID,
              new.VAL_ID,
              old.PLC_ID,
              new.PLC_ID,
              old.GRP_ID,
              new.GRP_ID,
              old.VAL_VARCHAR,
              new.VAL_VARCHAR,
              old.VAL_NUM,
              new.VAL_NUM,
              old.VAL_DATE,
              new.VAL_DATE,
              CURRENT_USER, NOW(), TG_OP);  
  ELSE IF TG_OP = 'DELETE' then
    insert into "SKLAD".Valueparamshistory(
			  HVAL_ID,
              OLD_VAL_ID,
              OLD_PLC_ID,
              OLD_GRP_ID,
              OLD_VAL_VARCHAR,
              OLD_VAL_NUM,
              OLD_VAL_DATE,              
              USERNAME,
              CHANGE_DATE,
              OPERATION)
    values (NEXTVAL('"SKLAD".SEQ_VALUEPARAMSHISTORY'),
			  old.VAL_ID,
              old.PLC_ID,
              old.GRP_ID,
              old.VAL_VARCHAR,
              old.VAL_NUM,
              old.VAL_DATE,
              CURRENT_USER, NOW(), TG_OP);    
  END IF;
  END IF;
  END IF;
  RETURN NEW;
EXCEPTION
WHEN others THEN
  raise;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TRIGGER "TRGVALUEPARAMS" AFTER INSERT OR UPDATE OR DELETE 
ON "SKLAD".valueparams FOR EACH ROW 
EXECUTE PROCEDURE "SKLAD"."F$TRGVALUEPARAMS"();